
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Tournaments_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Tournaments_Insert]

IF OBJECT_ID(N'[dbo].[Tournaments_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Tournaments_Update]

IF OBJECT_ID(N'[dbo].[Tournaments_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Tournaments_Delete]

IF OBJECT_ID(N'[dbo].[Tournaments_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Tournaments_Select]

--endregion

GO


--region [dbo].[Tournaments_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Tournaments_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdTournament int = null,
	@TourName nvarchar(100) = null,
	@IdCountryTournament int = null,
	@SortOrder int = null,
	@IsActived bit = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Tournaments]
					WHERE
					(
						(@IdTournament is null OR [Tournaments].[IdTournament] = @IdTournament)
						AND (@TourName is null OR [Tournaments].[TourName] like @TourName)
						AND (@IdCountryTournament is null OR [Tournaments].[IdCountryTournament] = @IdCountryTournament)
						AND (@SortOrder is null OR [Tournaments].[SortOrder] = @SortOrder)
						AND (@IsActived is null OR [Tournaments].[IsActived] = @IsActived)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Tournaments].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdTournament' and @SortType = 1 THEN [Tournaments].[IdTournament] END ASC,
						CASE WHEN @SortBy = 'IdTournament' and @SortType = 0 THEN [Tournaments].[IdTournament] END DESC,
						CASE WHEN @SortBy = 'TourName' and @SortType = 1 THEN [Tournaments].[TourName] END ASC,
						CASE WHEN @SortBy = 'TourName' and @SortType = 0 THEN [Tournaments].[TourName] END DESC,
						CASE WHEN @SortBy = 'IdCountryTournament' and @SortType = 1 THEN [Tournaments].[IdCountryTournament] END ASC,
						CASE WHEN @SortBy = 'IdCountryTournament' and @SortType = 0 THEN [Tournaments].[IdCountryTournament] END DESC,
						CASE WHEN @SortBy = 'SortOrder' and @SortType = 1 THEN [Tournaments].[SortOrder] END ASC,
						CASE WHEN @SortBy = 'SortOrder' and @SortType = 0 THEN [Tournaments].[SortOrder] END DESC,
						CASE WHEN @SortBy = 'IsActived' and @SortType = 1 THEN [Tournaments].[IsActived] END ASC,
						CASE WHEN @SortBy = 'IsActived' and @SortType = 0 THEN [Tournaments].[IsActived] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Tournaments]
			Where 
			(
				(@IdTournament is null OR [Tournaments].[IdTournament] = @IdTournament)
				AND (@TourName is null OR [Tournaments].[TourName] like @TourName)
				AND (@IdCountryTournament is null OR [Tournaments].[IdCountryTournament] = @IdCountryTournament)
				AND (@SortOrder is null OR [Tournaments].[SortOrder] = @SortOrder)
				AND (@IsActived is null OR [Tournaments].[IsActived] = @IsActived)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Tournaments_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Tournaments_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Tournaments_Insert]
	@IdTournament int OUTPUT,
	@TourName nvarchar(100),
	@IdCountryTournament int,
	@SortOrder int,
	@IsActived bit

AS


INSERT INTO [dbo].[Tournaments] 
(
	[TourName],
	[IdCountryTournament],
	[SortOrder],
	[IsActived]
)
VALUES 
(
	@TourName,
	@IdCountryTournament,
	@SortOrder,
	@IsActived
)

SET @IdTournament = SCOPE_IDENTITY()

--end [dbo].[Tournaments_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Tournaments_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Tournaments_Update]
	@IdTournament int,
	@TourName nvarchar(100),
	@IdCountryTournament int,
	@SortOrder int,
	@IsActived bit
AS


UPDATE [dbo].[Tournaments] SET
	[TourName] = @TourName,
	[IdCountryTournament] = @IdCountryTournament,
	[SortOrder] = @SortOrder,
	[IsActived] = @IsActived
WHERE
	[IdTournament] = @IdTournament

--end [dbo].[Tournaments_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Tournaments_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Tournaments_Delete]
	@IdTournament int
AS


DELETE FROM [dbo].[Tournaments]
WHERE
(
	[IdTournament] = @IdTournament
)

--end [dbo].[Tournaments_Delete]
--endregion

GO
--=========================================================================================--

